/*
 * SqliteInterface.java
 *
 * Created on 21. oktober 2007, 23:36
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

/**
 *
 * @author Dark_Wolf
 */
import java.net.URISyntaxException;
import java.sql.*;
import java.io.*;
import java.util.*;

import java.util.logging.Level;
import java.util.logging.Logger;
import org.sqlite.*;
public class SqliteInterface {
    static SqliteInterface instance = new SqliteInterface();
    private Connection conn=null;
    /** Creates a new instance of SqliteInterface */
    private  SqliteInterface() {
        try {

            
            File db = new File(getClass().getResource("../../../../../db/main@library.songbirdnest.com.db").toURI());
            Class.forName("org.sqlite.JDBC");
            System.out.println(db.getAbsolutePath());
            conn = DriverManager.getConnection("jdbc:sqlite:" + db.getAbsolutePath());
            System.out.println("SQLConnection created");
            /*
        } catch (URISyntaxException ex) {
           ex.printStackTrace();
             * */
        } catch (URISyntaxException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
            
              
            
         
       
        
            
        
    }
    public static SqliteInterface getInstance(){
        
        return instance;
    }
    public ArrayList<String> getURIsForAlbum(String album) {
        ArrayList<String> result = new ArrayList<String>();
       //System.out.println()
        try{
            PreparedStatement stat = conn.prepareStatement("SELECT DISTINCT content_url AS uri FROM (media_items NATURAL JOIN resource_properties) WHERE property_id=2 AND obj=?");
            stat.setString(1,album);
            ResultSet rs = stat.executeQuery();
            while (rs.next()){
                result.add(rs.getString("uri"));
            }
            
        } catch(Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    
    
   
    public ArrayList<String> getTracksForAlbum(String album){
         ArrayList<String> result = new ArrayList<String>();
       //System.out.println()
        try{
            PreparedStatement stat = conn.prepareStatement("SELECT DISTINCT r2.obj AS track FROM resource_properties AS r1, resource_properties AS r2 WHERE r1.property_id=2 AND r1.obj=? AND r2.media_item_id=r1.media_item_id AND r2.property_id=1");
            stat.setString(1,album);
            ResultSet rs = stat.executeQuery();
            while (rs.next()){
                result.add(rs.getString("track"));
            }
            
        } catch(Exception e) {
            e.printStackTrace();
        }
        return result;
    }
    
            
            
}
          
          
    
    

